from django.db import models
from django.db import transaction
from django.utils import timezone
from django.utils.dateparse import parse_datetime

class AIProvider(models.Model):
    name = models.CharField(max_length=100)
    base_url = models.URLField()
    api_key_alias = models.CharField(max_length=100, help_text="密钥别名（不明文存储）")
    timeout_seconds = models.PositiveIntegerField(default=20)
    enabled = models.BooleanField(default=True)
    # 新增：认证与请求配置（参考 api/test.py 的 headers 结构）
    api_key_header_name = models.CharField(max_length=100, default='Authorization', help_text="放置密钥的请求头键名，如 Authorization 或 x-gateway-apikey")
    api_key_prefix = models.CharField(max_length=50, default='Bearer ', help_text="密钥前缀，如 'Bearer '，如不需要留空")
    model_name = models.CharField(max_length=100, blank=True, help_text="模型名称（可选）")
    extra_headers = models.JSONField(default=dict, blank=True, help_text="附加请求头（JSON 对象），会与认证头合并")

    def __str__(self):
        return self.name

class Scene(models.Model):
    name = models.CharField(max_length=100, unique=True)
    description = models.TextField(blank=True)
    email_type = models.CharField(max_length=100, help_text="通过标题匹配的邮件类型关键词/正则")
    fields_to_extract = models.TextField(help_text="以逗号分隔的字段列表")
    prompt_template = models.TextField(help_text="提示词模板，支持变量")
    ai_provider = models.ForeignKey(AIProvider, on_delete=models.PROTECT)
    enabled = models.BooleanField(default=True)
    priority = models.PositiveIntegerField(default=10)
    # --- 新增：场景级更新策略配置 ---
    UPDATE_POLICY_CHOICES = [
        ("PO_ONLY", "仅当含PO再更新"),
        ("PO_OR_UNIQUE_ALT", "含PO或可唯一匹配的替代键时更新"),
        ("UNIQUE_ALT_ONLY", "仅当可唯一匹配替代键时更新（忽略PO）"),
        ("NEVER", "从不更新（仅记录结果)"),
    ]
    update_policy = models.CharField(max_length=30, choices=UPDATE_POLICY_CHOICES, default="PO_ONLY",
                                     help_text="控制何时更新Shipment宽表")
    alt_match_priority = models.CharField(max_length=200, blank=True,
                                          help_text="替代匹配键优先级（逗号分隔），填写 Shipment 字段名，按左到右优先级匹配")
    required_fields_for_update = models.TextField(blank=True,
                                                  help_text="允许更新前必须具备的字段（逗号分隔），为空表示不额外限制；默认PO_ONLY将内置要求po_no")
    allow_create_without_po = models.BooleanField(default=False, help_text="是否允许在无PO时按替代键创建Shipment（谨慎开启）")
    update_fields_whitelist = models.TextField(blank=True, help_text="限制可更新的字段白名单（逗号分隔），为空表示不限制")

    def __str__(self):
        return self.name

class Mailbox(models.Model):
    name = models.CharField(max_length=100)
    protocol = models.CharField(max_length=10, default='IMAP')
    host = models.CharField(max_length=200)
    port = models.PositiveIntegerField(default=993)
    username = models.CharField(max_length=200)
    password_alias = models.CharField(max_length=100, help_text="密码/应用专用密钥别名")
    folder = models.CharField(max_length=100, default='INBOX')
    use_ssl = models.BooleanField(default=True)
    enabled = models.BooleanField(default=True)
    forward_patterns = models.JSONField(default=list, blank=True, help_text="用于正文清洗的转发/历史分隔标识（正则字符串列表）")

    def __str__(self):
        return f"{self.name} ({self.username})"

class EmailMessage(models.Model):
    message_id = models.CharField(max_length=255, unique=True)
    subject = models.TextField()
    from_addr = models.CharField(max_length=255)
    to_addr = models.TextField()
    date = models.DateTimeField(null=True, blank=True)
    raw_path = models.CharField(max_length=500, blank=True)
    # 新增：正文与附件结构
    body_text = models.TextField(blank=True)
    body_html = models.TextField(blank=True)
    attachments = models.JSONField(default=list, blank=True, help_text="附件信息列表：[{'name':..., 'size':..., 'content_type':...}]")
    created_at = models.DateTimeField(auto_now_add=True)
    # 新增：来源邮箱与处理状态
    mailbox = models.ForeignKey(Mailbox, on_delete=models.PROTECT, null=True, blank=True)
    status = models.CharField(max_length=20, default='pending', db_index=True)  # pending/running/done/failed
    processed_at = models.DateTimeField(null=True, blank=True)
    last_error = models.TextField(blank=True)
    lock_token = models.CharField(max_length=64, blank=True)
    locked_at = models.DateTimeField(null=True, blank=True)

class ExtractionRun(models.Model):
    scene = models.ForeignKey(Scene, on_delete=models.PROTECT, null=True, blank=True)
    mailbox = models.ForeignKey(Mailbox, on_delete=models.PROTECT)
    email = models.ForeignKey(EmailMessage, on_delete=models.PROTECT)
    started_at = models.DateTimeField(auto_now_add=True)
    finished_at = models.DateTimeField(null=True, blank=True)
    status = models.CharField(max_length=20, default='pending')  # pending/running/success/failed
    error = models.TextField(blank=True)
    reasoning = models.TextField(blank=True)

class ExtractionResult(models.Model):
    run = models.ForeignKey(ExtractionRun, on_delete=models.CASCADE)
    field = models.CharField(max_length=100)
    value = models.TextField(blank=True)
    confidence = models.FloatField(default=0.0)
    source = models.CharField(max_length=50, default='ai')  # ai/rule/manual
    evidence = models.TextField(blank=True)

class Shipment(models.Model):
    """宽表：每个PO一行，随着识别更新。"""
    po_no = models.CharField(max_length=100, unique=True)
    booking_no = models.CharField(max_length=100, blank=True, db_index=True)
    bl_no = models.CharField(max_length=100, blank=True, db_index=True)
    vessel = models.CharField(max_length=200, blank=True)
    voyage = models.CharField(max_length=100, blank=True)
    pol = models.CharField(max_length=100, blank=True)
    pod = models.CharField(max_length=100, blank=True)
    container_no = models.CharField(max_length=100, blank=True, db_index=True)
    etd = models.DateTimeField(null=True, blank=True)
    eta = models.DateTimeField(null=True, blank=True)
    ata = models.DateTimeField(null=True, blank=True)
    pickup_date = models.DateTimeField(null=True, blank=True)
    updated_at = models.DateTimeField(auto_now=True, null=True, blank=True)

    def __str__(self):
        return self.po_no

# ----------------------
# 合并服务函数（可供抽取流水线/接口调用）
# ----------------------

FIELD_NAMES = {
    'booking_no', 'bl_no', 'vessel', 'voyage', 'pol', 'pod', 'container_no', 'etd', 'eta', 'ata', 'pickup_date'
}


def _csv_to_list(s: str):
    if not s:
        return []
    return [x.strip() for x in s.split(',') if x.strip()]


def _parse_dt(value):
    if value in (None, ""):
        return None
    if isinstance(value, (int, float)):
        return None
    if hasattr(value, 'isoformat'):
        return value
    try:
        dt = parse_datetime(str(value))
        return dt
    except Exception:
        return None


def _match_shipment_by_alt(scene: Scene, fields: dict):
    # 备选匹配字段（按优先级逐步收窄）
    alt_list = _csv_to_list(scene.alt_match_priority)
    if not alt_list:
        return None
    # 仅在 Shipment 模型真实存在的字段上匹配
    shipment_field_names = {f.name for f in Shipment._meta.get_fields() if hasattr(f, 'attname')}

    # 逐级过滤：从第一优先级开始，不断收窄候选集合
    qs = None  # 当前候选集合；None 表示尚未开始过滤
    for key in alt_list:
        if key not in shipment_field_names:
            continue
        val = fields.get(key)
        if not val:
            continue
        candidate_qs = Shipment.objects.filter(**{key: val}) if qs is None else qs.filter(**{key: val})
        count = candidate_qs.count()
        print(f"[DEBUG] Alt match try: {key}={val}, count={count}")
        if count == 1:
            return candidate_qs.first()
        if count > 1:
            qs = candidate_qs  # 继续在更小的集合上收窄
            continue
        # count == 0：忽略该键，保持现有 qs，不改变候选集合，继续尝试下一个优先级键

    # 所有可用替代键尝试完仍未唯一匹配
    return None


def _required_fields(scene: Scene):
    req = set(_csv_to_list(scene.required_fields_for_update))
    if scene.update_policy == 'PO_ONLY':
        req.add('po_no')
    return req


def _whitelist(scene: Scene):
    wl = set(_csv_to_list(scene.update_fields_whitelist))
    # 空白名单表示不限制（允许所有已知可更新字段）
    if not wl:
        return FIELD_NAMES
    # 只取模型已存在的字段交集，避免拼写错误带来的影响
    return wl.intersection(FIELD_NAMES)


@transaction.atomic
def process_extraction_and_merge(*, scene: Scene, mailbox: Mailbox, email: EmailMessage, results: dict, reasoning: str = ''):
    """
    results 结构示例：
    {
      'po_no': {'value': 'PO123', 'confidence': 0.95, 'source': 'ai', 'evidence': '...'},
      'booking_no': {'value': 'BK001', 'confidence': 0.9},
      ...
    }
    返回：{
      'run_id': int,
      'status': 'success'|'failed',
      'updated': bool,
      'shipment_po': str|None,
      'updated_fields': [str],
      'reason': str|None
    }
    """
    run = ExtractionRun.objects.create(scene=scene, mailbox=mailbox, email=email, status='running')
    if reasoning:
        run.reasoning = reasoning
        run.save(update_fields=['reasoning'])

    # 保存 ExtractionResult
    extracted_fields = {}
    for field, meta in (results or {}).items():
        value = ""
        conf = 0.0
        source = meta.get('source', 'ai') if isinstance(meta, dict) else 'ai'
        evidence = meta.get('evidence', '') if isinstance(meta, dict) else ''
        if isinstance(meta, dict):
            value = meta.get('value', '')
            try:
                conf = float(meta.get('confidence', 0.0))
            except Exception:
                conf = 0.0
        else:
            # 允许简单形式： 'field': 'value'
            value = meta
        ExtractionResult.objects.create(
            run=run, field=field, value=value or '', confidence=conf, source=source, evidence=evidence
        )
        extracted_fields[field] = value

    # 根据策略决定是否更新
    if scene.update_policy == 'NEVER':
        run.status = 'success'
        run.finished_at = timezone.now()
        run.save(update_fields=['status', 'finished_at'])
        return {'run_id': run.id, 'status': 'success', 'updated': False, 'shipment_po': None, 'updated_fields': [], 'reason': 'policy=NEVER'}
    
    req_fields = _required_fields(scene)
    for rf in req_fields:
        if not extracted_fields.get(rf):
            run.status = 'success'
            run.finished_at = timezone.now()
            run.save(update_fields=['status', 'finished_at'])
            return {'run_id': run.id, 'status': 'success', 'updated': False, 'shipment_po': None, 'updated_fields': [], 'reason': f'missing required field: {rf}'}

    po_val = (extracted_fields.get('po_no') or '').strip()
    shipment = None
    created = False

    policy = scene.update_policy
    if policy == 'PO_ONLY':
        if po_val: # 一定有PO,不然就在前面直接返回了"主题中无委托单号"
            shipment, created = Shipment.objects.get_or_create(po_no=po_val)
    elif policy == 'PO_OR_UNIQUE_ALT':
        if po_val:
            shipment, created = Shipment.objects.get_or_create(po_no=po_val)
        else:
            shipment = _match_shipment_by_alt(scene, extracted_fields)
    elif policy == 'UNIQUE_ALT_ONLY':
        # 无论是否提供PO，一律仅用替代键做唯一匹配
        shipment = _match_shipment_by_alt(scene, extracted_fields)
    # 其他策略在前面已返回

    if not shipment:
        # if scene.allow_create_without_po:
        #     alt_list = _csv_to_list(scene.alt_match_priority)
        #     if alt_list[0]: # 允许根据替代键,创建PO
        #         shipment, created = Shipment.objects.create(alt_list[0]=extracted_fields[alt_list[0]])
        run.status = 'success'
        run.finished_at = timezone.now()
        run.save(update_fields=['status', 'finished_at'])
        return {'run_id': run.id, 'status': 'success', 'updated': False, 'shipment_po': None, 'updated_fields': [], 'reason': 'no po or unique alt match'}

    # 生成可更新字段集合
    wl = _whitelist(scene)

    # 应用更新（保守：不以空覆盖非空；非空则更新）
    updated_fields = []
    for field in wl:
        if field not in FIELD_NAMES:
            continue
        new_val = extracted_fields.get(field)
        if new_val in (None, ""):
            continue
        # 日期字段解析
        if field in ('etd', 'eta', 'ata'):
            new_dt = _parse_dt(new_val)
            if new_dt is None:
                continue
            old_dt = getattr(shipment, field)
            if old_dt != new_dt:
                setattr(shipment, field, new_dt)
                updated_fields.append(field)
        else:
            old_val = getattr(shipment, field)
            if (old_val or "").strip() != str(new_val).strip():
                setattr(shipment, field, str(new_val).strip())
                updated_fields.append(field)

    # 附带把替代键（如果有值）也补全到 Shipment，以助后续匹配
    alt_list = _csv_to_list(scene.alt_match_priority)
    shipment_field_names = {f.name for f in Shipment._meta.get_fields() if hasattr(f, 'attname')}
    for key in alt_list:
        if key in wl:
            continue
        if key not in shipment_field_names:
            continue
        val = extracted_fields.get(key)
        if val and not getattr(shipment, key): # 允许补齐空值,但不允许有值覆盖
            setattr(shipment, key, str(val).strip())
            updated_fields.append(key)

    if updated_fields:
        shipment.save()

    run.status = 'success'
    run.finished_at = timezone.now()
    run.save(update_fields=['status', 'finished_at'])

    return {
        'run_id': run.id,
        'status': 'success',
        'updated': bool(updated_fields),
        'shipment_po': getattr(shipment, 'po_no', None),
        'updated_fields': sorted(set(updated_fields)),
        'reason': None
    }
